/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import beans.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Tomek
 */
public class DatabaseManager {

    private static final String DriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String ConnectionString = "jdbc:sqlserver://localhost\\sqlexpress2008;databaseName=Helpdesk";
    private static final String UserName = "Dev";
    private static final String Password = "123";
    private static Connection connection;

    public static boolean OpenConnection() {
        try {
            Class.forName(DriverName);
            connection = DriverManager.getConnection(ConnectionString, UserName, Password);
            return true;
        } catch (Exception ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public static void CloseConnection() {
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static int GetUserId(String login, String password) throws SQLException {
        String query = "{? = call GetUserId(?,?)}";
        CallableStatement statement = connection.prepareCall(query);
        statement.registerOutParameter(1, java.sql.Types.INTEGER); //userId
        statement.setString(2, login); //@login
        statement.setString(3, password); //@password
        ResultSet result = statement.executeQuery();
        if (result.next()) {
            return result.getInt("IdUser");
        } else {
            return -1;
        }
    }

    public static String GetUserName(int userId) throws SQLException {
        String query = "{? = call GetUserName(?)}";
        CallableStatement statement = connection.prepareCall(query);
        statement.registerOutParameter(1, java.sql.Types.NVARCHAR); //userName
        statement.setInt(2, userId); //@userId
        ResultSet result = statement.executeQuery();
        if (result.next()) {
            return result.getString("Name");
        } else {
            return "";
        }
    }

    public static ArrayList<Category> GetCategories() throws SQLException {
        ArrayList<Category> list = new ArrayList<Category>();
        String query = "{call GetCategories}";
        CallableStatement statement = connection.prepareCall(query);
        ResultSet result = statement.executeQuery();
        while (result.next()) {
            Category category = new Category();
            category.setId(result.getInt(1));
            category.setName(result.getString(2));
            list.add(category);
        }
        return list;
    }

    public static ArrayList<IssueResponse> GetIssueResponses(int issueId) throws SQLException {
        ArrayList<IssueResponse> list = new ArrayList<IssueResponse>();
        String query = "{call GetResponses(?)}";
        CallableStatement statement = connection.prepareCall(query);
        statement.setInt(1, issueId);
        ResultSet result = statement.executeQuery();
        while (result.next()) {
            IssueResponse response = new IssueResponse();
            response.setId(result.getInt(1));
            response.setIssueId(result.getInt(2));
            response.setAuthorId(result.getInt(3));
            response.setText(result.getString(4));
            response.setDate(result.getTimestamp(5));
            list.add(response);
        }
        return list;
    }

    public static int CreateUser(User user) throws SQLException {
        String query = "{call CreateAccount(?,?,?,?,?)}";
        CallableStatement statement = connection.prepareCall(query);
        //statement.registerOutParameter(1, java.sql.Types.INTEGER);
        statement.setString(1, user.getLogin());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getName());
        statement.setString(4, user.getSurname());
        statement.setString(5, user.getEmail());
        ResultSet result = statement.executeQuery();
        if (result.next()) {
            return result.getInt(1);
        } else {
            return 0;
        }
    }

    public static int AddIssue(Issue issue) throws SQLException {
        String query = "{call AddIssue(?,?)}";
        CallableStatement statement = connection.prepareCall(query);
        statement.setInt(1, issue.getCategoryId());
        statement.setString(2, issue.getTitle());
        ResultSet result = statement.executeQuery();
        if (result.next()) {
            return result.getInt(1);
        } else {
            return 0;
        }
    }

    public static int AddIssueResponse(IssueResponse response) throws SQLException {
        String query = "{call AddIssueResponse(?,?,?,?)}";
        CallableStatement statement = connection.prepareCall(query);
        statement.setInt(1, response.getIssueId());
        statement.setInt(2, response.getAuthorId());
        statement.setString(3, response.getText());
        Timestamp time = new Timestamp(Calendar.getInstance().getTimeInMillis());
        statement.setTimestamp(4, time);
        ResultSet result = statement.executeQuery();
        if (result.next()) {
            return result.getInt(1);
        } else {
            return 0;
        }
    }

    /*public static int AddIssueResponse(IssueResponse response) throws SQLException {
    Statement query = connection.createStatement();
    String queryString = String.format("insert into Users values ('%d','%d','%s','%s')", response.getIssueId(), response.getAuthorId(), response.getText(), response.getDate());
    return query.executeUpdate(queryString);
    }*/
}
